package com.willen.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class DBHelp<T> {
	private static String DBDRIVER;
	private static String ADDRESS;
	private static String NAME;
	private static String PASSWORD;
	private static BasicDataSource dataSource=initDataSource();
	private static BasicDataSource initDataSource() {
		Properties pop=new Properties();
		try {
			pop.load(DBHelp.class.getClassLoader().getResourceAsStream("dbconfig.properties"));
			DBDRIVER=pop.getProperty("driver");
			ADDRESS=pop.getProperty("url");
			NAME=pop.getProperty("name");
			PASSWORD=pop.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}
		BasicDataSource bds=new BasicDataSource();
		bds.setDriverClassName(DBDRIVER);
		bds.setUrl(ADDRESS);
		bds.setUsername(NAME);
		bds.setPassword(PASSWORD);
		bds.setInitialSize(2);
		bds.setMaxActive(10);
		bds.setMaxWait(3000);
		bds.setMinIdle(5);
		return bds;
	}
	public Connection getConnection() throws ClassNotFoundException, SQLException{
		return dataSource.getConnection();
	}
	public Number executeNumber(String sql,Object...obj){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Double result=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				ps.setObject(i+1, obj[i]);
			}
			rs=ps.executeQuery();
			while(rs.next()){
				result=rs.getDouble(1);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public void executeSQL(String sql,Object...obj){
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				ps.setObject(i+1, obj[i]);
			}
			ps.executeUpdate();
			System.out.println(sql);
			close(ps,conn);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List<T> executeForList(String sql,RowMapper<T> rowMapper,Object...obj){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<T> list=new ArrayList<T>();
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				ps.setObject(i+1, obj[i]);
			}
			rs=ps.executeQuery();
			while(rs.next()){
				list.add(rowMapper.add(rs));
			}
			System.out.println(sql);
			close(rs,ps,conn);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public T executeForObj(String sql,RowMapper<T> rowMapper,Object...obj){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		T result=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				ps.setObject(i+1, obj[i]);
			}
			rs=ps.executeQuery();
			while(rs.next()){
				result=rowMapper.add(rs);
			}
			System.out.println(sql);
			close(rs,ps,conn);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public void close(Statement ps,Connection conn){
		close(null,ps,conn);
	}
	public void close(ResultSet rs,Statement ps,Connection conn){
		try {
			if(rs!=null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(ps!=null){
					ps.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					if(conn!=null){
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
